﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class Ve_DAO
    {

        #region Kiem tra du lieu hop le va cac qui dinh
        //Cac ham kiem tra
        public static bool kiemTraMaVe(string maVe)
        {
            string query = "select * from ve where mave = '" + maVe + "'";
            if (SqlDataAccessHelper.ExecuteQuery(query).Rows.Count == 0)
                return false;
            return true;
        }
        //ham kiem tra ThoigianDatVe
        public static bool kiemTraTGianDatVe(Ve_DTO ve)
        {
            if (ve.LoaiVe)
                return true;
            LichBay_DTO lichBay = LichBay_DAO.layLichBayTheoMaLichBay(ve.MaLB);
            QuyDinh_DTO quyDinh = QuyDinh_DAO.layQuyDinh();
            if ((lichBay.ngayDi.Subtract(ve.NgayLap).TotalMinutes >= quyDinh.thoiGianDatVe))
                return true;
            return false;
        }

        #endregion

        


        #region Truy van du lieu
        //Ham lay mahk
        public static string layMaVe(string maHK)
        {
            string query = "select mave from ve where mahk = '" + maHK + "' ";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ve_tab == null)
                return null;
            return ve_tab.Select()[0]["MaVe"].ToString();
        }

        //Các hàm truy xuất dữ liệu
        public static List<Ve_DTO> layDanhSachVe()
        {
            string query = "select * from Ve";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ve_tab == null)
                return null;
            List<Ve_DTO> ve_list = new List<Ve_DTO>();
            int i = 0;
            foreach (DataRow dr in ve_tab.Rows)
            {
                ve_list.Add(new Ve_DTO());
                ve_list[i].MaVe = dr["MaVe"].ToString();
                ve_list[i].MaNV = dr["MaNV"].ToString();
                ve_list[i].MaHK = dr["MaHK"].ToString();
                ve_list[i].MaLB = dr["MaLB"].ToString();
                ve_list[i].HangVe = bool.Parse(dr["HangVe"].ToString());
                ve_list[i].LoaiVe = bool.Parse(dr["LoaiVe"].ToString());
                ve_list[i].NgayLap = DateTime.Parse(dr["NgayLap"].ToString());
                ve_list[i].SttGhe = int.Parse(dr["SttGhe"].ToString());
                i++;
            }
            return ve_list;
        }
        public static Ve_DTO layVeTheoMaVe(string maVe)
        {
            string query = "select * from Ve where mave = '" + maVe + "'";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ve_tab == null)
                return null;
            Ve_DTO ve = new Ve_DTO();
            DataRow dr = ve_tab.Rows[0];
            ve.MaVe = dr["MaVe"].ToString();
            ve.MaNV = dr["MaNV"].ToString();
            ve.MaHK = dr["MaHK"].ToString();
            ve.MaLB = dr["MaLB"].ToString();
            ve.HangVe = bool.Parse(dr["HangVe"].ToString());
            ve.LoaiVe = bool.Parse(dr["LoaiVe"].ToString());
            ve.NgayLap = DateTime.Parse(dr["NgayLap"].ToString());
            ve.SttGhe = int.Parse(dr["SttGhe"].ToString());
            return ve;
        }

        public static List<Ve_DTO> layVeTheoMaLB(string maLB)//CHO NAY CAN SUA LAI KIEU TRA VE
        {
            string query = "select * from Ve where malb = '" + maLB + "'";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ve_tab == null)
                return null;
            List<Ve_DTO> ve_list = new List<Ve_DTO>();

            int i = 0;
            foreach (DataRow dr in ve_tab.Rows)
            {
                ve_list.Add(new Ve_DTO());
                ve_list[i].MaVe = dr["MaVe"].ToString();
                ve_list[i].MaNV = dr["MaNV"].ToString();
                ve_list[i].MaHK = dr["MaHK"].ToString();
                ve_list[i].MaLB = dr["MaLB"].ToString();
                ve_list[i].HangVe = bool.Parse(dr["HangVe"].ToString());
                ve_list[i].LoaiVe = bool.Parse(dr["LoaiVe"].ToString());
                ve_list[i].NgayLap = DateTime.Parse(dr["NgayLap"].ToString());
                ve_list[i].SttGhe = int.Parse(dr["SttGhe"].ToString());
                i++;
            }
            return ve_list;
        }
        public static Ve_DTO layVeTheoMaHK(string maHK)
        {
            string query = "select * from Ve where mahk = '" + maHK + "'";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (ve_tab.Rows.Count ==0)
                return null;
            Ve_DTO ve = new Ve_DTO();
            DataRow dr = ve_tab.Rows[0];
            ve.MaVe = dr["MaVe"].ToString();
            ve.MaNV = dr["MaNV"].ToString();
            ve.MaHK = dr["MaHK"].ToString();
            ve.MaLB = dr["MaLB"].ToString();
            ve.HangVe = bool.Parse(dr["HangVe"].ToString());
            ve.LoaiVe = bool.Parse(dr["LoaiVe"].ToString());
            ve.NgayLap = DateTime.Parse(dr["NgayLap"].ToString());
            Console.WriteLine("{0}", ve.NgayLap);
            ve.SttGhe = int.Parse(dr["SttGhe"].ToString());
            return ve;
        }
        #endregion

        #region Thao tac insert, update, delete
        //Các hàm tương tác với cơ sở dữ liệu
        public static bool themVe(Ve_DTO ve)
        {
            string query = "insert into Ve(mave, malb, manv, mahk, hangve, loaive, sttghe, ngaylap)"
                            + " values ('" + (ve.MaVe + "', '") + (ve.MaLB + "', '") + (ve.MaNV + "', '") + (ve.MaHK + "', '")
                            + (ve.HangVe + "', '") + (ve.LoaiVe + "', '") + (ve.SttGhe + "', '") + (ve.NgayLap + "' ") + ")";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        public static bool suaVe(Ve_DTO ve)
        {
            string query = "Update Ve "
                            + "set " + ("mave = '" + ve.MaVe + "', ") + ("mahk = '" + ve.MaHK + "', ") + ("malb = '" + ve.MaLB + "', ") + ("manv = '" + ve.MaNV + "', ")
                            + ("hangve = '" + ve.HangVe.ToString() + "' , ") + ("loaive = '" + ve.LoaiVe.ToString() + "' , ")
                            + ("sttghe = " + ve.SttGhe + ", ") + ("ngaylap = '" + ve.NgayLap)
                            + "' where mave = '" + ve.MaVe + "'";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        public static bool xoaVe(Ve_DTO ve)
        {
            string query = "Delete Ve where mave = '" + ve.MaVe + "' ";
            int res = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (res != 0)
                return true;
            return false;
        }
        #endregion

        #region Thong Ke
        public static int soGheDat1(string maLB)
        {
            string query = "select MaVe from VE where MaLB = '" + maLB + "' and HangVe = 1 and loaiVe = 0";

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            return dt.Rows.Count;
        }
        public static int soGheDat2(string maLB)
        {
            string query = "select MaVe from VE where MaLB = '" + maLB + "' and HangVe = 0 and loaiVe = 0";

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            return dt.Rows.Count;
        }
        #endregion


        #region Truy van du lieu moi

        //public static int soGheDatHang1(string MaLB)
        //{
            
        //}

        //public static int soGheDatHang2(string MaLB)
        //{

        //}
        public static int soGheMuaHang1(string MaLB)
        {
            string query = "select count(maVe) soGheMua from Ve where loaiVe = 1 and hangVe = 1 and maLB = '" + MaLB + "'";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            return int.Parse(ve_tab.Select()[0]["soGheMua"].ToString());
        }
        public static int soGheMuaHang2(string MaLB)
        {
            string query = "select count(maVe) soGheMua from Ve where loaiVe = 1 and hangVe = 0 and maLB = '" + MaLB + "'";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            return int.Parse(ve_tab.Select()[0]["soGheMua"].ToString());
        }
        public static int soGheTrongHang1(string MaLB)
        {
            string query = "select CB.soGheHang1 soGheHang1 from ChuyenBay CB join LichBay LB on ( CB.maCB = LB.maCB) where LB.maLB = '" + MaLB + "'";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            return int.Parse(ve_tab.Select()[0]["soGheHang1"].ToString()) - Ve_DAO.soGheDat1(MaLB) - Ve_DAO.soGheMuaHang1(MaLB);
        }
        public static int soGheTrongHang2(string MaLB)
        {
            string query = "select CB.soGheHang2 soGheHang2 from ChuyenBay CB join LichBay LB on ( CB.maCB = LB.maCB) where LB.maLB = '" + MaLB + "'";
            DataTable ve_tab = SqlDataAccessHelper.ExecuteQuery(query);
            return int.Parse(ve_tab.Select()[0]["soGheHang1"].ToString()) - Ve_DAO.soGheDat2(MaLB) - Ve_DAO.soGheMuaHang2(MaLB);
        }


        #endregion
    }
}
